﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Net;
using System.Threading;
using System.Xml;
using Microsoft.WindowsAzure;
using Microsoft.WindowsAzure.Diagnostics;
using Microsoft.WindowsAzure.ServiceRuntime;
using System.Collections.Specialized;
using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling.SqlAzure;
using Microsoft.Practices.TransientFaultHandling;
using System.Data;
using System.Reflection;
using Microsoft.WindowsAzure.Diagnostics;
using Microsoft.WindowsAzure.ServiceRuntime;
using Microsoft.WindowsAzure.StorageClient;
using System.Text;

namespace WorkerRole.Main
{
    class BackupWorker
    {
        LogDataContext logContext;
        Microsoft.Practices.TransientFaultHandling.RetryPolicy azureRetryPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(3, TimeSpan.FromSeconds(30));

        private static StringBuilder exeOutput = new StringBuilder();

        public BackupWorker(LogDataContext _logContext)
        {
            logContext = _logContext;
        }

        public void Start(DatabaseInfo databaseInfo)
        {


            try
            {

                // check if we need to include federation members
                using (SqlConnection con = this.GetServerConnection(databaseInfo, databaseInfo.DatabaseName))
                {
                    con.OpenWithRetry(azureRetryPolicy);

                    try
                    {
                        string backupFileName = databaseInfo.DatabaseName + ".sql";
                        //BackupSqlScripts.GenerateSqlScripts(con,databaseInfo, backupFileName);
                        //UploadScriptToBlob(databaseInfo, backupFileName);
                    }
                    catch (Exception Ex)
                    {
                        logContext.SaveError("BackupWorker DB Script issue. The error: " + Ex.Message + " at " + DateTime.Now.ToString("ddMMMMyyyy HHMMSS"));
                    }

                    SqlCommand cmd = new SqlCommand(@"SELECT * FROM information_schema.tables", con);
                    DataTable dt = new DataTable();

                    using (SqlDataReader rd = cmd.ExecuteReader())
                    {
                        dt.Load(rd);
                        rd.Close();
                        foreach (DataRow dr in dt.Rows)
                        {
                            string backupFileName = databaseInfo.BackupFileName.Replace("@Date", DateTime.Now.ToString("yyyy-MM-dd")).Replace("@Time", DateTime.Now.ToString("HHmm")).Replace("@Table", dr["table_name"].ToString());
                            StartBcp(databaseInfo, dr["table_name"].ToString(), backupFileName);
                            StartZip(backupFileName);
                            UploadToBlob(databaseInfo,backupFileName);
                        }
                    }
                    con.Close();
                }
            }
            catch (Exception Ex)
            {
                logContext.SaveError("BackupWorker Start cause issue. The error: " + Ex.Message + " at " + DateTime.Now.ToString("ddMMMMyyyy HHMMSS"));

            }
        }


        private void StartBcp(DatabaseInfo databaseInfo,string tableName,string backupFileName)
        {
            int timeout = 30 * 60 * 1000; // 30 minutes timeout
            logContext.SaveEvent("Backup Started for table " + databaseInfo.DatabaseName + "." + tableName); 

            string path = Assembly.GetExecutingAssembly().Location;
            path = path.Substring(0, path.LastIndexOf("\\") + 1);

            using (Process process = new Process())
            {
                try {
                process.StartInfo.FileName = "bcp.exe";
                process.StartInfo.Arguments = getBcpArguments(databaseInfo, tableName, backupFileName);
                process.StartInfo.CreateNoWindow = false;
                process.StartInfo.ErrorDialog = false;
                process.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;                
                process.StartInfo.UseShellExecute = false;
                process.StartInfo.RedirectStandardOutput = true;
                process.StartInfo.RedirectStandardError = true;

                List<string> output = new List<string>();
                List<string> error = new List<string>();

                using (AutoResetEvent outputWaitHandle = new AutoResetEvent(false))
                using (AutoResetEvent errorWaitHandle = new AutoResetEvent(false))
                {
                    process.OutputDataReceived += (sender, e) =>
                    {
                        if (e.Data == null)
                        {
                            outputWaitHandle.Set();
                        }
                        else
                        {
                            output.Add(e.Data);
                            if (output.Count > 50) output.RemoveAt(0);
                        }
                    };
                    process.ErrorDataReceived += (sender, e) =>
                    {
                        if (e.Data == null)
                        {
                            errorWaitHandle.Set();
                        }
                        else
                        {
                            error.Add(e.Data);
                            if (error.Count > 50) error.RemoveAt(0);
                        }
                    };

                    process.Start();

                    process.BeginOutputReadLine();
                    process.BeginErrorReadLine();

                    if (process.WaitForExit(timeout) &&
                        outputWaitHandle.WaitOne(timeout) &&
                        errorWaitHandle.WaitOne(timeout))
                    {
                        logContext.SaveEvent("StartBcp Process Messages: " + string.Join("\n", output.ToArray()));
                    }
                    else
                    {
                        logContext.SaveEvent("StartBcp Process Messages: " + string.Join("\n", output.ToArray()));
                        logContext.SaveEvent("StartBcp Process ErrorMessages: " + string.Join("\n", error.ToArray()));
                    }
                }
                     }
            catch (Exception Ex)
            {
                logContext.SaveError("StartBcp cause issue. The error: " + Ex.ToString() + " at " + DateTime.Now.ToString("ddMMMMyyyy HHMMSS"));
            }

            }
        }




        private void StartZip(string backupFileName)
        {
            int timeout = 30 * 60 * 1000; // 30 minutes timeout
            logContext.SaveEvent("Zip Started for filr " + backupFileName);

            string path = Assembly.GetExecutingAssembly().Location;
            path = path.Substring(0, path.LastIndexOf("\\") + 1);

            using (Process process = new Process())
            {
                try
                {
                    process.StartInfo.FileName = "7za.exe";
                    process.StartInfo.Arguments = String.Format("a -r -mx=1 -tzip C:\\bcp-zip\\{0}.zip C:\\bcp\\{0}", backupFileName);
                    process.StartInfo.CreateNoWindow = false;
                    process.StartInfo.ErrorDialog = false;
                    process.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
                    process.StartInfo.UseShellExecute = false;
                    process.StartInfo.RedirectStandardOutput = true;
                    process.StartInfo.RedirectStandardError = true;

                    List<string> output = new List<string>();
                    List<string> error = new List<string>();

                    using (AutoResetEvent outputWaitHandle = new AutoResetEvent(false))
                    using (AutoResetEvent errorWaitHandle = new AutoResetEvent(false))
                    {
                        process.OutputDataReceived += (sender, e) =>
                        {
                            if (e.Data == null)
                            {
                                outputWaitHandle.Set();
                            }
                            else
                            {
                                output.Add(e.Data);
                                if (output.Count > 50) output.RemoveAt(0);
                            }
                        };
                        process.ErrorDataReceived += (sender, e) =>
                        {
                            if (e.Data == null)
                            {
                                errorWaitHandle.Set();
                            }
                            else
                            {
                                error.Add(e.Data);
                                if (error.Count > 50) error.RemoveAt(0);
                            }
                        };

                        process.Start();

                        process.BeginOutputReadLine();
                        process.BeginErrorReadLine();

                        if (process.WaitForExit(timeout) &&
                            outputWaitHandle.WaitOne(timeout) &&
                            errorWaitHandle.WaitOne(timeout))
                        {
                            logContext.SaveEvent("StartZip Process Messages: " + string.Join("\n",output.ToArray()));
                        }
                        else
                        {
                            logContext.SaveEvent("StartZip Process Messages: " + string.Join("\n", output.ToArray()));
                            logContext.SaveEvent("StartZip Process ErrorMessages: " + string.Join("\n", error.ToArray()));
                        }
                    }
                }
                catch (Exception Ex)
                {
                    logContext.SaveError("StartZip cause issue. The error: " + Ex.ToString() + " at " + DateTime.Now.ToString("ddMMMMyyyy HHMMSS"));
                }

            }
        }




        private void UploadToBlob(DatabaseInfo databaseInfo,string backupFileName)
        {
            logContext.SaveEvent("Upload to Blob Started for file " + backupFileName);


            string sourceBackupFileName = String.Format("C:\\bcp-zip\\{0}.zip", backupFileName);
            CloudBlobClient blobStorage;
            try {
           // read account configuration settings
           string strConnectionString = "DefaultEndpointsProtocol=" + databaseInfo.BlobDefaultEndpointsProtocol + ";AccountName=" + databaseInfo.StorageAccountName + ";AccountKey=" + databaseInfo.StorageAccessKey;
           var storageAccount = CloudStorageAccount.Parse(strConnectionString);

                // create blob container for backup data
                blobStorage = storageAccount.CreateCloudBlobClient();
                CloudBlobContainer container = blobStorage.GetContainerReference(databaseInfo.BlobContainerName);
                container.CreateIfNotExist();

                // configure container for private access
                var permissions = container.GetPermissions();
                permissions.PublicAccess = BlobContainerPublicAccessType.Off;
                container.SetPermissions(permissions);

                CloudBlockBlob blob = container.GetBlockBlobReference(backupFileName + ".zip");

                blob.Metadata["Server"] = databaseInfo.ServerName;
                blob.Metadata["Database"] = databaseInfo.DatabaseName;
                blob.Metadata["FileName"] = databaseInfo.BackupFileName;
                blob.Metadata["BackupDate"] = DateTime.Now.ToString("dd-MMM-yyyy HH:MM");

                blob.UploadFile(sourceBackupFileName);

                System.IO.File.Delete(sourceBackupFileName);
                System.IO.File.Delete("C:\\bcp\\" + backupFileName);

            }

            catch (Exception ex)
            {
                logContext.SaveError("UploadToBlob cause error", ex.Message + "\n" + ex.StackTrace);
            }
            finally
            {
            }


        }




        private void UploadScriptToBlob(DatabaseInfo databaseInfo, string backupFileName)
        {
            logContext.SaveEvent("Upload Script to Blob Started for file " + backupFileName);


            string sourceBackupFileName = String.Format("C:\\bcp\\{0}", backupFileName);
            CloudBlobClient blobStorage;
            try
            {
                // read account configuration settings
                string strConnectionString = "DefaultEndpointsProtocol=" + databaseInfo.BlobDefaultEndpointsProtocol + ";AccountName=" + databaseInfo.StorageAccountName + ";AccountKey=" + databaseInfo.StorageAccessKey;
                var storageAccount = CloudStorageAccount.Parse(strConnectionString);

                // create blob container for backup data
                blobStorage = storageAccount.CreateCloudBlobClient();
                CloudBlobContainer container = blobStorage.GetContainerReference(databaseInfo.BlobContainerName);
                container.CreateIfNotExist();

                // configure container for private access
                var permissions = container.GetPermissions();
                permissions.PublicAccess = BlobContainerPublicAccessType.Off;
                container.SetPermissions(permissions);

                CloudBlockBlob blob = container.GetBlockBlobReference(backupFileName);

                blob.Metadata["Server"] = databaseInfo.ServerName;
                blob.Metadata["Database"] = databaseInfo.DatabaseName;
                blob.Metadata["FileName"] = databaseInfo.BackupFileName;
                blob.Metadata["BackupDate"] = DateTime.Now.ToString("dd-MMM-yyyy HH:MM");

                blob.UploadFile(sourceBackupFileName);

                System.IO.File.Delete(sourceBackupFileName);
                System.IO.File.Delete("C:\\bcp\\" + backupFileName);

            }

            catch (Exception ex)
            {
                logContext.SaveError("UploadScriptToBlob cause error", ex.Message + "\n" + ex.StackTrace);
            }
            finally
            {
            }


        }


        private string getBcpArguments(DatabaseInfo databaseInfo, string tableName, string backupFileName)
        {

            string Bcp= String.Format (
                "\"[{5}].[dbo].{0}\" out \"C:\\bcp\\{1}\" -E -n -S {2} -U \"{3}\" -P {4}",
                tableName,
                backupFileName,
                databaseInfo.ServerName,
                databaseInfo.LoginId,
                databaseInfo.Password,
                databaseInfo.DatabaseName);

            return Bcp;
        }
        

        SqlConnection GetServerConnection(DatabaseInfo databaseInfo, string databaseName)
        {
            SqlConnectionStringBuilder sqlConnectionStringBuilder = new SqlConnectionStringBuilder();
            sqlConnectionStringBuilder.DataSource = databaseInfo.ServerName;
            sqlConnectionStringBuilder.InitialCatalog = databaseName;
            sqlConnectionStringBuilder.Encrypt = databaseInfo.UseSSL;
            sqlConnectionStringBuilder.IntegratedSecurity = false;
            sqlConnectionStringBuilder.UserID = databaseInfo.LoginId;
            sqlConnectionStringBuilder.Password = databaseInfo.Password;            
            sqlConnectionStringBuilder.ConnectTimeout = 60;
            sqlConnectionStringBuilder.MinPoolSize = 5;
            sqlConnectionStringBuilder.MaxPoolSize = 50;        // According to Azure team there is NO max limit on connections.  It all comes down to CPU and IO usage.  They said setting this to 100 would be fine.
            sqlConnectionStringBuilder.Pooling = true;

            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = sqlConnectionStringBuilder.ToString();
            return connection;
        }
    }

}
